package com.gljx.web.controller;


import com.gljx.constant.ActivitiKey;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import com.gljx.web.service.CustomLogService;
import com.gljx.web.service.DepartmentService;
import com.gljx.web.service.SubjectFileEntryService;
import com.gljx.web.service.SubjectFileService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.text.SimpleDateFormat;
import java.util.*;



@Controller
@RequestMapping("/")
public class SubjectFileController {

	@Autowired
	private SubjectFileService subjectFileService;
	
	@Autowired
	private DepartmentService departmentService;
	
	@Autowired
	private SubjectFileEntryService subjectFileEntryService;
	
	@Autowired
	private StudentController studentController;
	
	@Autowired
	private CustomLogService customLogService;


	ActitiviUtil actitiviUtil = new ActitiviUtil();
	/**
	 * 查询科目一档案交接
	 * @param request
	 * @return
	 * @createTime 2017-11-24 10:16 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/selectSubjectFile/look.action")
	public String selectSubjectFile(HttpServletRequest request) throws Exception {
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
			     ca.setTime(new Date()); // 设置时间为当前时间
			     ca.add(Calendar.DATE, -1);// 月份减1
		Date resultDate = ca.getTime(); // 结果
		HttpSession session=request.getSession();
		User user=(User) session.getAttribute("user");                                   //获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String beginTime = sdf.format(resultDate);
		String endTime = sdf.format(new Date());	
		PageBean<SubjectFile> pBean = getPageBean(request, department.getFid(), beginTime, endTime);
		//		rhc  信息总条数
		String messageAmount="共有"+pBean.getTr()+"条信息";
		request.setAttribute("pb", pBean);
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("ActivitiKey",ActivitiKey.Driving_GL_ID.equals(GetUserSession.getUserSession(request)) ? ActivitiKey.DrivingSys_fileAndSkill_gl : ActivitiKey.DrivingSys_fileAndSkill_nc);
		request.setAttribute("subjectFiles", pBean.getBeanList());
		//		rhc  信息总条数
		request.setAttribute("messageAmount",messageAmount);
		return "backHome/subjectFile/subjectFileList";
	}
	
	
	/**
	 * 查询副表
	 * @param fid
	 * @createTime 2017-11-25 9:46 am
	 * @author zyq.
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value="/selSubjectFileEntryByFid")
	public List<SubjectFileEntry> selSubjectFileEntryByFid(String fid) {
		List<SubjectFileEntry> subjectFileEntries = subjectFileEntryService.selSubjectFileEntryByFid(fid);
		return subjectFileEntries;
	}
	
	
	/**
	 * 新增科目一档案
	 * @param request
	 * @return
	 * @createTime 2017-11-25 11:46 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/addSubjectFile")
	public String addSubjectFile(HttpServletRequest request) throws Exception {
		HttpSession session=request.getSession();
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
		         ca.setTime(new Date()); // 设置时间为当前时间
		         ca.add(Calendar.DATE, -1);// 月份减1
	    Date resultDate = ca.getTime(); // 结果
	    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	    String beginTime = sdf.format(resultDate);
	    String endTime = sdf.format(new Date());
		User user=(User) session.getAttribute("user");                                  		//获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());  	   //获取登录用户组织ID
        Map map = studentController.getPageAsTrainCharge(request, "", "", "0","","","", beginTime, endTime); //调用学员方法    //调用学员方法
		request.setAttribute("flag", 1);				//是否为新增记录	
		request.setAttribute("beginTime", beginTime);
        request.setAttribute("endTime", endTime);
		request.setAttribute("pb", map.get("pb"));
        request.setAttribute("students", map.get("sList"));
        request.setAttribute("department", department);
		return "backHome/subjectFile/detailSubjectFile";
	}
	
	
	/**
	 * 保存科目一档案交接
	 * @param request
	 * @createTime 2017-11-27 10:46 am
	 * @author zyq.
	 * @param subjectFile
	 * @param subjectFileEntry
	 * @return
	 */
	@RequestMapping(value="/saveSubjectFile")
	public String saveSubjectFile(HttpServletRequest request ,SubjectFile subjectFile, SubjectFileEntry subjectFileEntry,String cfsex1) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//利用UUID生成fid
		subjectFile.setFid(GetFid.UUIDIntoFid());
		//控制单元id
		subjectFile.setFcontrolunitid(user.getFcompanyid());
		//创建人id
		subjectFile.setFcreatorid(user.getFid());
		//公司id
		subjectFile.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String createTime = TimeFormat.timeFormat(new Date());
		subjectFile.setFcreatetime(createTime);
        /*String fnumber = customLogService.selectFunmber("CT_DS_SUBJECTONEFILEHANDOVER",user.getFcompanyid());*/
        subjectFile.setFnumber(GetFnumber.fnumbers("DNJJ"));
		subjectFile.setCfbillstate((long) 3);
		subjectFile.setFhandlerid(user.getFid());
		if(cfsex1 != null){
		if(cfsex1.length() > 1){
		String [] cfsex = cfsex1.split(",");
		String [] cfclazz = (String[]) subjectFileEntry.getCfclazz();
		String [] studentid = subjectFileEntry.getCfstudentid().split(",");
		String [] cfidcardno = (String[]) subjectFileEntry.getCfidcardno();
		String [] cfstudentno = (String[]) subjectFileEntry.getCfstudentno();
		String [] cfcx = subjectFileEntry.getCfcx().split(",");
		String [] cfphone = (String[]) subjectFileEntry.getCfphone();
		String [] cfdesc = (String[]) subjectFileEntry.getCfdesc();
		for(int i=0;i<cfsex.length;i++) {
			subjectFileEntry.setCfclazz(cfclazz[i]);
			subjectFileEntry.setCfcx(cfcx[i]);
			subjectFileEntry.setCfdesc(cfdesc[i]);
			subjectFileEntry.setCfidcardno(cfidcardno[i]);
			subjectFileEntry.setCfstudentno(cfstudentno[i]);
			subjectFileEntry.setCfstudentid(studentid[i]);
			subjectFileEntry.setCfphone(cfphone[i]);
			Long sex ;
			if("null".equals(cfsex[i]) || "".equals(cfsex[i]))
				sex = null;
			else
				sex = new Long(cfsex[i]);
			subjectFileEntry.setCfsex(sex);
			subjectFileEntry.setFid(GetFid.UUIDIntoFid());
			subjectFileEntry.setFparentid(subjectFile.getFid());
			subjectFileEntryService.insertSubjectFileEntry(subjectFileEntry);              //保存副表记录
			customLogService.addLog("科目一档案交接", "办理了学员："+subjectFileEntry.getCfstudentno()+subjectFileEntry.getCfstudentName()+"科目一档案交接,单据编号："+subjectFile.getFnumber(), studentid[i], user.getFid());
		}
		}else{
			Long sex = new Long(cfsex1);
			subjectFileEntry.setCfsex(sex);
			subjectFileEntry.setFid(GetFid.UUIDIntoFid());
			subjectFileEntry.setFparentid(subjectFile.getFid());
			subjectFileEntryService.insertSubjectFileEntry(subjectFileEntry);              //保存副表记录
			customLogService.addLog("科目一档案交接","办理了学员："+subjectFileEntry.getCfstudentno()+subjectFileEntry.getCfstudentName()+"科目一档案交接,单据编号："+subjectFile.getFnumber(), subjectFile.getFid(), user.getFid());
		}
		subjectFileService.insertSubjectFile(subjectFile);
		}else{
			
		}
		request.setAttribute("type",2);
		return "forward:addSubjectFile";
	}
	
	
	/**
	 * 根据FID查询档案交接
	 * @param request
	 * @param fid
	 * @return
	 * @createTime 2017-11-28 9:46 am
	 * @author zyq.
	 * @throws Exception 
	 */
	@RequestMapping(value="/selSubjectFileById")
	public String selSubjectFileById(HttpServletRequest request,String fid) throws Exception{
		SubjectFile subjectFile = subjectFileService.selectSubjectFileByFid(fid);
		if(subjectFile == null)
			return "/error/not_data";
		else {
			if(subjectFile.getCfbillstate() == 3 ){
				Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
				        ca.setTime(new Date()); // 设置时间为当前时间
				        ca.add(Calendar.DATE, -1);// 月份减1
				Date resultDate = ca.getTime(); // 结果
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String beginTime = sdf.format(resultDate);
				String endTime = sdf.format(new Date());	
				List<SubjectFileEntry> subjectFileEntries = subjectFileEntryService.selSubjectFileEntryByFid(fid);
                Map map = studentController.getPageAsTrainCharge(request, "", "", "0","","","", beginTime, endTime); //调用学员方法 //调用学员方法
				request.setAttribute("beginTime", beginTime);
		        request.setAttribute("endTime", endTime);
				request.setAttribute("pb", map.get("pb"));
		        request.setAttribute("students", map.get("sList"));
				request.setAttribute("subjectFile", subjectFile);
				request.setAttribute("subjectFileEntries", subjectFileEntries);
				request.setAttribute("flag", 2);	
			}else{
				List<SubjectFileEntry> subjectFileEntries = subjectFileEntryService.selSubjectFileEntryByFid(fid);
				request.setAttribute("subjectFile", subjectFile);
				request.setAttribute("subjectFileEntries", subjectFileEntries);
				request.setAttribute("flag", 2);	
			}
		//2.查询
		String result = actitiviUtil.getTaskStateByUser(
				ActivitiKey.Driving_GL_ID.equals(GetUserSession.getUserSession(request)) ? ActivitiKey.DrivingSys_fileAndSkill_gl : ActivitiKey.DrivingSys_fileAndSkill_nc
				, subjectFile.getFnumber()               //单据
		);
		String seeFlag = subjectFileService.selectAuditPersonByAdd(subjectFile.getFnumber(),result);
		request.setAttribute("seeFlag",seeFlag);
		request.setAttribute("ActivitiKey",ActivitiKey.Driving_GL_ID.equals(GetUserSession.getUserSession(request)) ? ActivitiKey.DrivingSys_fileAndSkill_gl : ActivitiKey.DrivingSys_fileAndSkill_nc);
		request.setAttribute("submitFlag",result);
		return "backHome/subjectFile/detailSubjectFile";
		}
	}
	
	
	/**
	 * 审核档案一交接
	 * @param request
	 * @param fid
	 * @return
	 * @createTime 2017-11-28 8:46 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/checkSubjectFileBillState")
    @ResponseBody
	public ServerResponse checkSubjectFileBillState(HttpServletRequest request,String fid,String state,String agreed,String auditcomment,String fnumber,String submitFlag) throws Exception {
		/*User user=(User) request.getSession().getAttribute("user");
		SubjectFile subjectFile = new SubjectFile();
					subjectFile.setFauditorid(user.getFid());
					subjectFile.setCfbillstate((long) 5);
					subjectFile.setFid(fid);
					subjectFileService.updateSubjectFile(subjectFile);
		List<SubjectFileEntry> subjectFileEntries = subjectFileEntryService.selSubjectFileEntryByFid(fid);
		Student student = new Student();
		for(SubjectFileEntry subjectFileEntry:subjectFileEntries){
			student.setCfdajj((long) 1);
			student.setFid(subjectFileEntry.getCfstudentid());
			studentService.updateStudentSelective(student);
		}
		customLogService.addLog("档案一交接", user.getName() + TimeFormat.timeFormat(new Date()) + " 审核了档案一交接 ", subjectFile.getFid(), user.getFid());  //生成日志
		selectSubjectFile(request);	*/
        User user=(User) request.getSession().getAttribute("user");
		List<SubjectFileEntry> subjectFileEntries = subjectFileEntryService.selSubjectFileEntryByFid(fid);
		SubjectFile subjectFile = new SubjectFile();
                    subjectFile.setFauditorid(user.getFid());
                    subjectFile.setCfbillstate((long) 5);
                    subjectFile.setFid(fid);
                    subjectFile.setFnumber(fnumber);
		for(SubjectFileEntry subjectFileEntry :subjectFileEntries) {
			if ("3".equalsIgnoreCase(state))
				customLogService.addLog("科目一档案交接", "反审核了学员"+subjectFileEntry.getCfstudentno()+subjectFileEntry.getCfstudentName() + "科目一档案交接 ,单据编号："+subjectFile.getFnumber(), subjectFileEntry.getCfstudentid(), user.getFid());  //生成日志
			else
				customLogService.addLog("科目一档案交接", "审核了学员"+subjectFileEntry.getCfstudentno()+subjectFileEntry.getCfstudentName() + "科目一档案交接 ,单据编号："+subjectFile.getFnumber(), subjectFileEntry.getCfstudentid(), user.getFid());  //生成日志
		}
        return ServerResponse.createBySuccessMessage(subjectFileService.checkSubject(subjectFileEntries,subjectFile,state,agreed,auditcomment,submitFlag,request));
	}
	
	
	/**
	 * 更新档案一技能交接表
	 * @param request
	 * @param subjectFile
	 * @param subjectFileEntry
	 * @return
	 * @createTime 2017-11-28 9:46 am
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/updateSubjectFile")
	public String updateSubjectFile(HttpServletRequest request,SubjectFile subjectFile, SubjectFileEntry subjectFileEntry,String cfsex1) throws Exception {
		User user=(User) request.getSession().getAttribute("user");
		String [] fids = subjectFile.getFid().split(",");              //批量fid，用来删除副表
		String updateTime=TimeFormat.timeFormat(new Date());
		subjectFile.setFlastupdatetime(updateTime);
		subjectFile.setFlastupdateuserid(user.getFid());
		subjectFileService.updateSubjectFile(subjectFile);
		subjectFileEntryService.delSubjectFileEntryByPid(fids);
		if(cfsex1.length() > 1){
		String [] cfsex = cfsex1.split(",");
		String [] cfclazz = (String[]) subjectFileEntry.getCfclazz();
		String [] studentid = subjectFileEntry.getCfstudentid().split(",");
		String [] cfidcardno = (String[]) subjectFileEntry.getCfidcardno();
		String [] cfstudentno = (String[]) subjectFileEntry.getCfstudentno();
		String [] cfcx = subjectFileEntry.getCfcx().split(",");
		String [] cfphone = (String[]) subjectFileEntry.getCfphone();
		String [] cfdesc = (String[]) subjectFileEntry.getCfdesc();
		for(int i=0;i<cfsex.length;i++) {
			subjectFileEntry.setCfclazz(cfclazz[i]);
			subjectFileEntry.setCfcx(cfcx[i]);
			subjectFileEntry.setCfdesc(cfdesc[i]);
			subjectFileEntry.setCfidcardno(cfidcardno[i]);
			subjectFileEntry.setCfstudentno(cfstudentno[i]);
			subjectFileEntry.setCfstudentid(studentid[i]);
			subjectFileEntry.setCfphone(cfphone[i]);
			Long sex ;
			if("null".equals(cfsex[i]) || "".equals(cfsex[i]))
				sex = null;
			else
				sex = new Long(cfsex[i]);
			subjectFileEntry.setCfsex(sex);
			subjectFileEntry.setFid(GetFid.UUIDIntoFid());
			subjectFileEntry.setFparentid(subjectFile.getFid());
			subjectFileEntryService.insertSubjectFileEntry(subjectFileEntry);              //保存副表记录
		}
		}else{
			Long sex = new Long(cfsex1);
			subjectFileEntry.setCfsex(sex);
			subjectFileEntry.setFid(GetFid.UUIDIntoFid());
			subjectFileEntry.setFparentid(subjectFile.getFid());
			subjectFileEntryService.insertSubjectFileEntry(subjectFileEntry);              //保存副表记录
		}
		selectSubjectFile(request);
		request.setAttribute("type",2);
		return "backHome/subjectFile/subjectFileList";
	}

    /**
     * 提交
     * @param fnumber
     * @return
     */
	@RequestMapping(value = "/submitSubjectFile")
    @ResponseBody
	public ServerResponse submitSubjectFile(String fnumber,String fid,String submitFlag,HttpServletRequest request){
		User user=(User) request.getSession().getAttribute("user");
		SubjectFile subjectFile = new SubjectFile();
					subjectFile.setFid(fid);
					subjectFile.setFnumber(fnumber);
					subjectFile.setCfbillstate((long) 4);
		SubjectFile subjectFile2 = subjectFileService.selectSubjectFileByFid(fid);
		List<SubjectFileEntry> subjectFileEntries = subjectFileEntryService.selSubjectFileEntryByFid(subjectFile2.getFid());
		subjectFileService.submitFlow(subjectFile,submitFlag,request);
		subjectFileService.updateSubjectFile(subjectFile);
		if(subjectFileEntries !=null) {
			for(SubjectFileEntry subjectFileEntry : subjectFileEntries) {
				customLogService.addLog("科目一档案交接", "提交了学员："+subjectFileEntry.getCfstudentno()+subjectFileEntry.getCfstudentName()+"科目一档案交接,单据编号："+subjectFile2.getFnumber(), subjectFileEntry.getCfstudentid(), user.getFid());
			}
		}
        return ServerResponse.createBySuccessMessage("提交成功");
	}
	
	
	/**
	 * 批量删除记录
	 * @param request
	 * @param fid
	 * @createTime 2017-11-27 11:46 am
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/delSubjectFileById")
	public String  delSubjectFileById(HttpServletRequest request ,String fid) throws Exception {
		String [] fids = fid.split(",");
		subjectFileService.delSubjectFile(fids);
		subjectFileEntryService.delSubjectFileEntryByPid(fids);
		selectSubjectFile(request);
		return "backHome/subjectFile/subjectFileList";
	}
	
	
	/**
	 * 根据条件查询科目一档案交接
	 * @param request
	 * @param subjectFile
	 * @param subjectFileEntry
	 * @createTime 2017-11-27 2:16 pm
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/selSubjectFileByCon")
	public String selSubjectFileByCon(HttpServletRequest request ,SubjectFile subjectFile ,SubjectFileEntry subjectFileEntry,String beginTime,String endTime) throws Exception {
		HttpSession session=request.getSession();
		User user=(User) session.getAttribute("user");                                   //获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
		PageBean<SubjectFile> pBean = getPageBean1(request, department.getFid(), beginTime, endTime, subjectFile, subjectFileEntry,false);
		//		rhc  信息总条数
		String messageAmount="共有"+pBean.getTr()+"条信息";

		request.setAttribute("pb", pBean);
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("subjectFiles", pBean.getBeanList());
		request.setAttribute("subjectFile1", subjectFile);
		request.setAttribute("subjectFileEntry1", subjectFileEntry);
		request.setAttribute("ActivitiKey",ActivitiKey.Driving_GL_ID.equals(GetUserSession.getUserSession(request)) ? ActivitiKey.DrivingSys_fileAndSkill_gl : ActivitiKey.DrivingSys_fileAndSkill_nc);
		//		rhc  信息总条数
		request.setAttribute("messageAmount",messageAmount);
		if(("".equals(subjectFile.getFnumber())|| null == subjectFile.getFnumber())&& ("".equals(subjectFileEntry.getCfstudentName()) || null == subjectFileEntry.getCfstudentName()) && ("".equals(subjectFileEntry.getCfidcardno()) || null == subjectFileEntry.getCfidcardno())){
			return "backHome/subjectFile/subjectFileList";
		}else{
			return "backHome/subjectFile/subjectFileList1";
		}
	}

	@RequestMapping(value = "/outSubjectFileExcel")
	public void outSubjectFileExcel(HttpServletResponse response, HttpServletRequest request,
							  SubjectFileEntry SubjectFileEntry, SubjectFile SubjectFile, String beginTime, String endTime) throws Exception {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		List<Object> data = new ArrayList<>();
		//查询数据
		PageBean<SubjectFile> pBean =  getPageBean1(request,user.getFcompanyid(),beginTime,endTime,SubjectFile,SubjectFileEntry,true);
		for (SubjectFile subjectFile1 : pBean.getBeanList()) {
			data.add(subjectFile1.getFnumber());//单据编号
			data.add(subjectFile1.getFbizdate());//业务日期
			data.add(subjectFile1.getFdescription());
			data.add(subjectFile1.getFauditorName());
			data.add(subjectFile1.getFcreateName());//制单人名称
			data.add(subjectFile1.getCfstudentName());//学员
			data.add(subjectFile1.getCfstudentno());//学员编码
			data.add(subjectFile1.getCfidcardno());//身份证号
			data.add(subjectFile1.getCfdesc());
			if (subjectFile1.getCfbillstate() == 3) {
				data.add("保存");//单据状态
			} else if (subjectFile1.getCfbillstate() == 4) {
				data.add("提交");//单据状态
			} else if (subjectFile1.getCfbillstate() == 5) {
				data.add("审核");//单据状态
			} else {
				data.add("");//单据状态
			}
			if (subjectFile1.getCfsex() == null) {
				data.add("");//性别
			} else if (subjectFile1.getCfsex() == 2) {
				data.add("女");//性别
			} else {
				data.add("男");//性别
			}
			data.add(subjectFile1.getCfcx());
			data.add(subjectFile1.getCfphone());
			data.add(subjectFile1.getCfclazz());

		}
		//头部数据
		String head[] = {"单据编码", "业务日期", "备注","审核人名称", "制单人名称","学员","学员编码", "身份证号码","备注","单据状态", "性别",
				"车型", "手机", "班级"};
		//长度
		Integer length[] = {7500, 3000, 6000, 3000, 3000, 3000, 6200, 6200, 6000, 3000,
				3000, 2600, 4000, 6000};
		try {
			//导出Excel
			CommonExport.importExcel(response, "科目一档案交接" + TimeFormat.getNowTime2() + ".xls", "科目一档案交接", data, head, length);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}



	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<SubjectFile> getPageBean(HttpServletRequest request,String fid,String beginTime,String endTime) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		String psql="select  T.FID,to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
				   +"to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"  
				   + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
				   + "T.FCREATORID, T.FCONTROLUNITID,T.FLASTUPDATEUSERID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
				   + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID,T.CFBILLSTATE, "
				   + "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME"
			   		+ "  FROM CT_DS_SUBJECTONEFILEHANDOVER T "
			   		+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
			   		+ " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID "
			   		+ " WHERE t.CFFICOMPANYID= '"+fid+"' and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '"+beginTime+"' AND '"+endTime+"'";
		psql +="  order by to_char(t.FBIZDATE,'yyyy-MM-dd') desc";
		int psize=10; //每页显示数
		int pindex=offset/psize;//+1  页码从0开始	
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= subjectFileService.selectPage(pindex, psql, psize);
		PageBean<SubjectFile> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<SubjectFile>)paramMap.get("v_cur")); //每页数据集
		return pb;

	}

	
	
	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<SubjectFile> getPageBean1(HttpServletRequest request,String fid,String beginTime,String endTime,SubjectFile subjectFile,SubjectFileEntry subjectFileEntry,boolean isExcel) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}
		String psql = "";
		if(("".equals(subjectFile.getFnumber())|| null == subjectFile.getFnumber())&& ("".equals(subjectFileEntry.getCfstudentName()) || null == subjectFileEntry.getCfstudentName())){
			if(isExcel){
				psql = "select J.CFSTUDENTNO, j.CFIDCARDNO, j.CFDESC, j.CFPHONE, j.CFSEX, " +
						"j.CFCX, j.CFCLAZZ，u.fname_l2 as cfstudentName,T.FID,to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
						+ "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
						+ "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
						+ "T.FCREATORID, T.FCONTROLUNITID,T.FLASTUPDATEUSERID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
						+ "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID,T.CFBILLSTATE, "
						+ "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME"
						+ "  FROM CT_DS_SUBJECTONEFILEHANDOVER T "
						+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
						+ " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID "
						+ "LEFT JOIN CT_DS_SUBJECTOFHE J ON T.FID = J.FPARENTID LEFT JOIN CT_DS_STUDENT U ON U.FID = J.CFSTUDENTID"
						+ " WHERE t.CFFICOMPANYID= '" + fid + "' ";
				if (subjectFile.getFnumber().equals(null) || subjectFile.getFnumber() == "") {

				} else {
					psql += "AND T.FNUMBER LIKE '%" + subjectFile.getFnumber() + "%'";
				}
				if (subjectFileEntry.getCfidcardno().equals(null) || subjectFileEntry.getCfidcardno() == "") {

				} else {
					psql += "AND J.CFIDCARDNO LIKE '%" + subjectFileEntry.getCfidcardno() + "%'";
				}
				if (subjectFileEntry.getCfstudentName().equals(null) || subjectFileEntry.getCfstudentName() == "") {

				} else {
					psql += "AND U.FNAME_L2 LIKE '%" + subjectFileEntry.getCfstudentName() + "%'";
				}
				if (beginTime.equals(null) || beginTime.equals("") || endTime.equals(null) || endTime.equals("")) {
				} else {
					psql += "and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
				}
				psql += "order by FBIZDATE desc";
			}else {
				psql = "select  T.FID,to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
						+ "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
						+ "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
						+ "T.FCREATORID, T.FCONTROLUNITID,T.FLASTUPDATEUSERID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
						+ "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID,T.CFBILLSTATE, "
						+ "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME"
						+ "  FROM CT_DS_SUBJECTONEFILEHANDOVER T "
						+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
						+ " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID "
						+ " WHERE t.CFFICOMPANYID= '" + fid + "'";
				if (beginTime.equals(null) || beginTime.equals("") || endTime.equals(null) || endTime.equals("")) {
				} else {
					psql += " and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
				}
				psql += " order by to_char(t.FBIZDATE,'yyyy-MM-dd') desc";
			}
		}else{
			 psql = "select J.CFSTUDENTNO, j.CFIDCARDNO, j.CFDESC, j.CFPHONE, j.CFSEX, " +
					"j.CFCX, j.CFCLAZZ，u.fname_l2 as cfstudentName,T.FID,to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
					+ "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
					+ "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
					+ "T.FCREATORID, T.FCONTROLUNITID,T.FLASTUPDATEUSERID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
					+ "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID,T.CFBILLSTATE, "
					+ "S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME"
					+ "  FROM CT_DS_SUBJECTONEFILEHANDOVER T "
					+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
					+ " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID "
					+ "LEFT JOIN CT_DS_SUBJECTOFHE J ON T.FID = J.FPARENTID LEFT JOIN CT_DS_STUDENT U ON U.FID = J.CFSTUDENTID"
					+ " WHERE t.CFFICOMPANYID= '" + fid + "' ";
			if (subjectFile.getFnumber() == null || subjectFile.getFnumber() == "") {

			} else {
				psql += "AND T.FNUMBER LIKE '%" + subjectFile.getFnumber() + "%'";
			}
			if (subjectFileEntry.getCfidcardno() == null || subjectFileEntry.getCfidcardno() == "") {

			} else {
				psql += "AND J.CFIDCARDNO LIKE '%" + subjectFileEntry.getCfidcardno() + "%'";
			}
			if (subjectFileEntry.getCfstudentName() == null || subjectFileEntry.getCfstudentName() == "") {

			} else {
				psql += "AND U.FNAME_L2 LIKE '%" + subjectFileEntry.getCfstudentName() + "%'";
			}
			if (beginTime.equals(null) || beginTime.equals("") || endTime.equals(null) || endTime.equals("")) {
			} else {
				psql += "and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
			}
			psql += "order by FBIZDATE desc";
		}
		Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
		int psize = 0;
		if(isExcel)
			if((int)paramMapCount.get("Pcount") == 0)
				psize = 1;
			else
				psize = (int)paramMapCount.get("Pcount");
		else
			psize=80; //每页显示数
		int pindex=offset/psize;//+1  页码从0开始	
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= subjectFileService.selectPage(pindex, psql, psize);
		PageBean<SubjectFile> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<SubjectFile>)paramMap.get("v_cur")); //每页数据集
		return pb;

	}


	/**
	 * 取记录总数 dmz 20160921
	 * @param psql  SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception
	{

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap=subjectFileService.selectPageCount(psql);
		return paramMap;		
	}




}
